Number Functions¶
ABS¶
-
ABS(number_expr)¶ The ABS function returns the absolute value of a given number. The data type of the return value is the same as that of the argument.
Parameters: number_expr -- An operator which returns a numeric value Return type: same as that of the argument
--it returns the absolute value of the argument
SELECT ABS(12.3), ABS(-12.3), ABS(-12.3000), ABS(0.0);
abs(12.3) abs(-12.3) abs(-12.3000) abs(0.0)
================================================================================
12.3 12.3 12.3000 .0
ACOS¶
-
ACOS(x)¶ The ACOS function returns an arc cosine value of the argument. That is, it returns a value whose cosine is x in radian. The return value is a DOUBLE type. x must be a value between -1 and 1, inclusive. Otherwise, NULL is returned.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT ACOS(1), ACOS(0), ACOS(-1);
acos(1) acos(0) acos(-1)
==================================================================================
0.000000000000000e+00 1.570796326794897e+00 3.141592653589793e+00
ASIN¶
-
ASIN(x)¶ The ASIN function returns an arc sine value of the argument. That is, it returns a value whose sine is x in radian. The return value is a DOUBLE type. x must be a value between -1 and 1, inclusive. Otherwise, NULL is returned.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT ASIN(1), ASIN(0), ASIN(-1);
asin(1) asin(0) asin(-1)
==============================================================================
1.570796326794897e+00 0.000000000000000e+00 -1.570796326794897e+00
ATAN¶
-
ATAN([y, ]x)¶ The ATAN function returns a value whose tangent is x in radian. The argument y can be omitted. If y is specified, the function calculates the arc tangent value of y/x. The return value is a DOUBLE type.
Parameters: x,y -- An expression that returns a numeric value Return type: DOUBLE
SELECT ATAN(1), ATAN(-1), ATAN(1,-1);
atan(1) atan(-1) atan2(1, -1)
==============================================================================
7.853981633974483e-01 -7.853981633974483e-01 2.356194490192345e+000
ATAN2¶
-
ATAN2(y, x)¶ The ATAN2 function returns the arc tangent value of y/x in radian. This function is working like the
ATAN(). Arguments x and y must be specified. The return value is a DOUBLE type.Parameters: x,y -- An expression that returns a numeric value Return type: DOUBLE
SELECT ATAN2(1,1), ATAN2(-1,-1), ATAN2(Pi(),0);
atan2(1, 1) atan2(-1, -1) atan2( pi(), 0)
==============================================================================
7.853981633974483e-01 -2.356194490192345e+00 1.570796326794897e+00
CEIL¶
-
CEIL(number_operand)¶ The CEIL function returns the smallest integer that is not less than its argument. The return value is determined based on the valid number of digits that are specified as the number_operand argument.
Parameters: number_operand -- An expression that returns a numeric value Return type: INT
SELECT CEIL(34567.34567), CEIL(-34567.34567);
ceil(34567.34567) ceil(-34567.34567)
============================================
34568.00000 -34567.00000
SELECT CEIL(34567.1), CEIL(-34567.1);
ceil(34567.1) ceil(-34567.1)
=============================
34568.0 -34567.0
CONV¶
-
CONV(number, from_base, to_base)¶ The CONV function converts numbers between different number bases. This function returns a string representation of a converted number. The minimum value is 2 and the maximum value is 36. If to_base (representing the base to be returned) is negative, number is regarded as a signed number. Otherwise, it regarded as a unsigned number.
Parameters: - number -- An input number
- from_base -- The base of an input number
- to_base -- The base of an returned value
Return type: STRING
SELECT CONV('f',16,2);
'1111'
SELECT CONV('6H',20,8);
'211'
SELECT CONV(-30,10,-20);
'-1A'
COS¶
-
COS(x)¶ The COS function returns a cosine value of the argument. The argument x must be a radian value. The return value is a DOUBLE type.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT COS(pi()/6), COS(pi()/3), COS(pi());
cos( pi()/6) cos( pi()/3) cos( pi())
==============================================================================
8.660254037844387e-01 5.000000000000001e-01 -1.000000000000000e+00
COT¶
-
COT(x)¶ The COT function returns the cotangent value of the argument x. That is, it returns a value whose tangent is x in radian. The return value is a DOUBLE type.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT COT(1), COT(-1), COT(0);
cot(1) cot(-1) cot(0)
==========================================================================
6.420926159343306e-01 -6.420926159343306e-01 NULL
DEGREES¶
-
DEGREES(x)¶ The DEGREES function returns the argument x specified in radian converted to a degree value. The return value is a DOUBLE type.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT DEGREES(pi()/6), DEGREES(pi()/3), DEGREES (pi());
degrees( pi()/6) degrees( pi()/3) degrees( pi())
==============================================================================
3.000000000000000e+01 5.999999999999999e+01 1.800000000000000e+02
DRANDOM, DRAND¶
-
DRANDOM([seed])¶
-
DRAND([seed])¶ The function DRANDOM or DRAND returns a random double-precision floating point value in the range of between 0.0 and 1.0. A seed argument that is INTEGER type can be specified. It rounds up real numbers and an error is returned when it exceeds the range of INTEGER.
The DRAND function performs the operation only once to produce only one random number regardless of the number of rows where the operation is output, but the DRANDOM function performs the operation every time the statement is repeated to produce a different random value for each row. Therefore, to output rows in a random order, you must use the DRANDOM function in the ORDER BY clause. To obtain a random integer value, use the
RANDOM().Parameters: seed -- Return type: DOUBLE
SELECT DRAND(), DRAND(1), DRAND(1.4);
drand() drand(1) drand(1.4)
==============================================================================
2.849646518006921e-001 4.163034446537495e-002 4.163034446537495e-002
SELECT * FROM rand_tbl;
id name
===================================
1 'a'
2 'b'
3 'c'
4 'd'
5 'e'
6 'f'
7 'g'
8 'h'
9 'i'
10 'j'
--drandom() returns random values on every row
SELECT DRAND(), DRANDOM() FROM rand_tbl;
drand() drandom()
==============================================================================
7.638782921842098e-001 1.018707846308786e-001
7.638782921842098e-001 3.191320535905026e-001
7.638782921842098e-001 3.461714529862361e-001
7.638782921842098e-001 6.791894283883175e-001
7.638782921842098e-001 4.533829767754143e-001
7.638782921842098e-001 1.714224677266762e-001
7.638782921842098e-001 1.698049867244484e-001
7.638782921842098e-001 4.507583849604786e-002
7.638782921842098e-001 5.279091769157994e-001
7.638782921842098e-001 7.021088290047914e-001
--selecting rows in random order
SELECT * FROM rand_tbl ORDER BY DRANDOM();
id name
===================================
6 'f'
2 'b'
7 'g'
8 'h'
1 'a'
4 'd'
10 'j'
9 'i'
5 'e'
3 'c'
EXP¶
-
EXP(x)¶ The EXP function returns e x (the base of natural logarithm) raised to a power.
Parameters: x -- An operator which returns a numeric value Return type: DOUBLE
SELECT EXP(1), EXP(0);
exp(1) exp(0)
====================================================
2.718281828459045e+000 1.000000000000000e+000
SELECT EXP(-1), EXP(2.00);
exp(-1) exp(2.00)
====================================================
3.678794411714423e-001 7.389056098930650e+000
FLOOR¶
-
FLOOR(number_operand)¶ The FLOOR function returns the largest integer that is not greater than its argument. The data type of the return value is the same as that of the argument.
Parameters: number_operand -- An operator which returns a numeric value Return type: same as that of the argument
--it returns the largest integer less than or equal to the arguments
SELECT FLOOR(34567.34567), FLOOR(-34567.34567);
floor(34567.34567) floor(-34567.34567)
============================================
34567.00000 -34568.00000
SELECT FLOOR(34567), FLOOR(-34567);
floor(34567) floor(-34567)
=============================
34567 -34567
HEX¶
-
HEX(n)¶ The HEX function returns a decimal string if a hexadecimal string is specified as an argument; it returns a hexadecimal string if a decimal string is specified as an argument. If a number is specified as an argument, it returns a value like CONV(num, 10, 16).
Parameters: n -- A hexadecimal string or A decimal string Return type: STRING
SELECT HEX('ab'), HEX(128), CONV(HEX(128), 16, 10);
hex('ab') hex(128) conv(hex(128), 16, 10)
==================================================================
'6162' '80' '128'
LN¶
-
LN(x)¶ The LN function returns the natural log value (base = e) of an antilogarithm x. The return value is a DOUBLE type. If the antilogarithm is 0 or a negative number, an error is returned.
Parameters: x -- An expression that returns a positive number Return type: DOUBLE
SELECT ln(1), ln(2.72);
ln(1) ln(2.72)
=====================================================
0.000000000000000e+00 1.000631880307906e+00
LOG2¶
-
LOG2(x)¶ The LOG2 function returns a log value whose antilogarithm is x and base is 2. The return value is a DOUBLE type. If the antilogarithm is 0 or a negative number, an error is returned.
Parameters: x -- An expression that returns a positive number Return type: DOUBLE
SELECT log2(1), log2(8);
log2(1) log2(8)
======================================================
0.000000000000000e+00 3.000000000000000e+00
LOG10¶
-
LOG10(x)¶ The LOG10 function returns the common log value of an antilogarithm x. The return value is a DOUBLE type. If the antilogarithm is 0 or a negative number, an error is returned.
Parameters: x -- An expression that returns a positive number Return type: DOUBLE
SELECT log10(1), log10(1000);
log10(1) log10(1000)
====================================================
0.000000000000000e+00 3.000000000000000e+00
MOD¶
-
MOD(m, n)¶ The MOD function returns the remainder of the first parameter m divided by the second parameter n. If n is 0, m is returned without the division operation being performed. Note that if the dividend, the parameter m of the MOD function, is a negative number, the function operates differently from a typical operation (classical modulus) method.
Result of MOD
m n MOD(m, n) Classical Modulus m-n*FLOOR(m/n) 11 4 3 3 11 -4 3 -1 -11 4 -3 1 -11 -4 -3 -3 11 0 11 Divided by 0 error Parameters: - m -- Represents a dividend. It is an expression that returns a numeric value.
- n -- Represents a divisor. It is an expression that returns a numeric value.
Return type: INT
--it returns the reminder of m divided by n
SELECT MOD(11, 4), MOD(11, -4), MOD(-11, 4), MOD(-11, -4), MOD(11,0);
mod(11, 4) mod(11, -4) mod(-11, 4) mod(-11, -4) mod(11, 0)
=====================================================================
3 3 -3 -3 11
SELECT MOD(11.0, 4), MOD(11.000, 4), MOD(11, 4.0), MOD(11, 4.000);
mod(11.0, 4) mod(11.000, 4) mod(11, 4.0) mod(11, 4.000)
=========================================================================
3.0 3.000 3.0 3.000
PI¶
-
PI()¶ The PI function returns the π value of type DOUBLE.
Return type: DOUBLE
SELECT PI(), PI()/2;
pi() pi()/2
====================================================
3.141592653589793e+00 1.570796326794897e+00
POW, POWER¶
-
POW(x, y)¶
-
POWER(x, y)¶ The POW function returns x to the power of y. The functions POW and POWER are used interchangeably. The return value is a DOUBLE type.
Parameters: - x -- It represents the base. It is an expression that returns a numeric value. An expression that returns a numeric value.
- y -- It represents the exponent. An expression that returns a numeric value. If the base is a negative number, an integer must specified as the exponent.
Return type: DOUBLE
SELECT POWER(2, 5), POWER(-2, 5), POWER(0, 0), POWER(1,0);
power(2, 5) power(-2, 5) power(0, 0) power(1, 0)
======================================================================================
3.200000000000000e+01 -3.200000000000000e+01 1.000000000000000e+00 1.000000000000000e+00
--it returns an error when the negative base is powered by a non-int exponent
SELECT POWER(-2, -5.1), POWER(-2, -5.1);
ERROR: Argument of power() is out of range.
RADIANS¶
-
RADIANS(x)¶ The RADIANS function returns the argument x specified in degrees converted to a radian value. The return value is a DOUBLE type.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT RADIANS(90), RADIANS(180), RADIANS(360);
radians(90) radians(180) radians(360)
==============================================================================
1.570796326794897e+00 3.141592653589793e+00 6.283185307179586e+00
RANDOM, RAND¶
-
RANDOM([seed])¶
-
RAND([seed])¶ The function RANDOM or RAND returns any integer value, which is greater than or equal to 0 and less than 2 31, and a seed argument that is INTEGER type can be specified. It rounds up real numbers and an error is returned when it exceeds the range of INTEGER.
The RAND function performs the operation only once to produce only one random number regardless of the number of rows where the operation is output, but the RANDOM function performs the operation every time the statement is repeated to produce a different random value for each row. Therefore, to output rows in a random order, you must use the RANDOM function. To obtain a random real number, use the
DRANDOM().Parameters: seed -- Return type: INT
SELECT RAND(), RAND(1), RAND(1.4);
rand() rand(1) rand(1.4)
=======================================
1526981144 89400484 89400484
--creating a new table
SELECT * FROM rand_tbl;
id name
===================================
1 'a'
2 'b'
3 'c'
4 'd'
5 'e'
6 'f'
7 'g'
8 'h'
9 'i'
10 'j'
--random() returns random values on every row
SELECT RAND(),RANDOM() FROM rand_tbl;
rand() random()
============================
2078876566 1753698891
2078876566 1508854032
2078876566 625052132
2078876566 279624236
2078876566 1449981446
2078876566 1360529082
2078876566 1563510619
2078876566 1598680194
2078876566 1160177096
2078876566 2075234419
--selecting rows in random order
SELECT * FROM rand_tbl ORDER BY RANDOM();
id name
===================================
6 'f'
1 'a'
5 'e'
4 'd'
2 'b'
7 'g'
10 'j'
9 'i'
3 'c'
8 'h'
ROUND¶
-
ROUND(number_operand, integer)¶ The ROUND function returns the specified argument, number_operand, rounded to the number of places after the decimal point specified by the integer. If the integer argument is a negative number, it rounds to a place before the decimal point, that is, at the integer part.
Parameters: - number_operand -- An expression that returns a numeric value
- integer -- Specifies the place to round to. If a positive integer n is specified, the number is represented to the nth place after the decimal point; if a negative integer n is specified, the number is rounded to the n th place before the decimal point.
Return type: same type as the number_operand
--it rounds a number to one decimal point when the second argument is omitted
SELECT ROUND(34567.34567), ROUND(-34567.34567);
round(34567.34567, 0) round(-34567.34567, 0)
============================================
34567.00000 -34567.00000
--it rounds a number to three decimal point
SELECT ROUND(34567.34567, 3), ROUND(-34567.34567, 3) FROM db_root;
round(34567.34567, 3) round(-34567.34567, 3)
============================================
34567.34600 -34567.34600
--it rounds a number three digit to the left of the decimal point
SELECT ROUND(34567.34567, -3), ROUND(-34567.34567, -3);
round(34567.34567, -3) round(-34567.34567, -3)
============================================
35000.00000 -35000.00000
SIGN¶
-
SIGN(number_operand)¶ The SIGN function returns the sign of a given number. It returns 1 for a positive value, -1 for a negative value, and 0 for zero.
Parameters: number_operand -- An operator which returns a numeric value Return type: INT
--it returns the sign of the argument
SELECT SIGN(12.3), SIGN(-12.3), SIGN(0);
sign(12.3) sign(-12.3) sign(0)
========================================
1 -1 0
SIN¶
-
SIN(x)¶ The SIN function returns a sine value of the parameter. The argument x must be a radian value. The return value is a DOUBLE type.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT SIN(pi()/6), SIN(pi()/3), SIN(pi());
sin( pi()/6) sin( pi()/3) sin( pi())
==============================================================================
4.999999999999999e-01 8.660254037844386e-01 1.224646799147353e-16
SQRT¶
-
SQRT(x)¶ The SQRT function returns the square root of x as a DOUBLE type.
Parameters: x -- An expression that returns a numeric value. An error is returned if this value is a negative number. Return type: DOUBLE
SELECT SQRT(4), SQRT(16.0);
sqrt(4) sqrt(16.0)
====================================================
2.000000000000000e+00 4.000000000000000e+00
TAN¶
-
TAN(x)¶ The TAN function returns a tangent value of the argument. The argument x must be a radian value. The return value is a DOUBLE type.
Parameters: x -- An expression that returns a numeric value Return type: DOUBLE
SELECT TAN(pi()/6), TAN(pi()/3), TAN(pi()/4);
tan( pi()/6) tan( pi()/3) tan( pi()/4)
==============================================================================
5.773502691896257e-01 1.732050807568877e+00 9.999999999999999e-01
TRUNC, TRUNCATE¶
-
TRUNC(x[, dec])¶
-
TRUNCATE(x, dec)¶ The function TRUNC or TRUNCATE truncates the numbers of the specified argument x to the right of the dec position. If the dec argument is a negative number, it displays 0s to the dec- th position left to the decimal point. Note that the dec argument of the TRUNC function can be omitted, but that of the TRUNCATE function cannot be omitted. If the dec argument is a negative number, it displays 0s to the dec -th position left to the decimal point. The number of digits of the return value to be represented follows the argument x.
Parameters: - x -- An expression that returns a numeric value
- dec -- The place to be truncated is specified. If a positive integer n is specified, the number is represented to the n-th place after the decimal point; if a negative integer n is specified, the number is truncated to the n-th place before the decimal point. It truncates to the first place after the decimal point if the dec argument is 0 or omitted. Note that the dec argument cannot be omitted in the TRUNCATE function.
Return type: same type as the x
--it returns a number truncated to 0 places
SELECT TRUNC(34567.34567), TRUNCATE(34567.34567, 0);
trunc(34567.34567, 0) trunc(34567.34567, 0)
============================================
34567.00000 34567.00000
--it returns a number truncated to three decimal places
SELECT TRUNC(34567.34567, 3), TRUNC(-34567.34567, 3);
trunc(34567.34567, 3) trunc(-34567.34567, 3)
============================================
34567.34500 -34567.34500
--it returns a number truncated to three digits left of the decimal point
SELECT TRUNC(34567.34567, -3), TRUNC(-34567.34567, -3);
trunc(34567.34567, -3) trunc(-34567.34567, -3)
============================================
34000.00000 -34000.00000
WIDTH_BUCKET¶
-
WIDTH_BUCKET(expression, from, to, num_buckets)¶ WIDTH_BUCKET distributes the rows in an ordered partition into a specified number of buckets. The buckets are numbered, starting from one. That is, WIDTH_BUCKET function creates an equi-width histogram. The return value is an integer.
This function equally divides the range by the given number of buckets and assigns the bucket number to each bucket. That is, every interval (bucket) has the identical size.
Note that
NTILE()function equally divides the number of rows by the given number of buckets and assigns the bucket number to each bucket. That is, every bucket has the same number of rows.Parameters: - expression -- an input value to assign the bucket number. It specifies a certain expression which returns the number.
- from -- a start value of the range, which is given to expression. It is included in the entire range.
- to -- an end value of the range, which is given to expression. It is not included in the entire range.
- num_buckets -- the number of buckets. The #0 bucket and the #(num_buckets + 1) bucket are created to include the contents beyond the range.
Return type: INT
expression is an input value to assign the bucket number. from and to should be numeric values, date/time values, or the string which can be converted to date/time value. from is included in the acceptable range, but to is beyond the range.
For example, WIDTH_BUCKET (score, 80, 50, 3) returns
- 0 when the score is larger than 80,
- 1 for [80, 70),
- 2 for [70, 60),
- 3 for [60, 50),
- and 4 when the score is 50 or smaller.
The following example divides the range equal to 80 or smaller and larger than 50 into the score range that has the identical score range from 1 to 3. If any score is beyond the range, 0 is given for the score larger than 80 and 4 is given for the score of 50 or smaller than 50.
CREATE TABLE t_score (name VARCHAR(10), score INT);
INSERT INTO t_score VALUES
('Amie', 60),
('Jane', 80),
('Lora', 60),
('James', 75),
('Peter', 70),
('Tom', 50),
('Ralph', 99),
('David', 55);
SELECT name, score, WIDTH_BUCKET (score, 80, 50, 3) grade
FROM t_score
ORDER BY grade ASC, score DESC;
name score grade
================================================
'Ralph' 99 0
'Jane' 80 1
'James' 75 1
'Peter' 70 2
'Amie' 60 3
'Lora' 60 3
'David' 55 3
'Tom' 50 4
In the following example, WIDTH_BUCKET function evenly divides the birthdate range into buckets and assigns the bucket number based on the range. It divides the range of eight customers from '1950-01-01' to '1999-12-31' into five buckets based on their dates of birth. If the birthdate value is beyond the range, 0 or 6 (num_buckets + 1) is returned.
CREATE TABLE t_customer (name VARCHAR(10), birthdate DATE);
INSERT INTO t_customer VALUES
('Amie', date'1978-03-18'),
('Jane', date'1983-05-12'),
('Lora', date'1987-03-26'),
('James', date'1948-12-28'),
('Peter', date'1988-10-25'),
('Tom', date'1980-07-28'),
('Ralph', date'1995-03-17'),
('David', date'1986-07-28');
SELECT name, birthdate, WIDTH_BUCKET (birthdate, date'1950-01-01', date'2000-1-1', 5) age_group
FROM t_customer
ORDER BY birthdate;
name birthdate age_group
===============================================
'James' 12/28/1948 0
'Amie' 03/18/1978 4
'Tom' 07/28/1980 4
'Jane' 05/12/1983 5
'David' 07/28/1986 5
'Lora' 03/26/1987 5
'Peter' 10/25/1988 5
'Ralph' 03/17/1995 6